﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using CNVP.Framework.Helper;
using CNVP.Config;
using CNVP.Framework.Utils;

namespace CNVP.JShop.DAL
{
    public class CNVP_Ads
    {
        private string Prefix = DBConfig.Prefix;
        #region DAL
        public int Insert(Model.CNVP_Ads obj, byte IsType)
        {
            StringBuilder sql = new StringBuilder();
            if (IsType == 1)
            {
                sql.Append("insert into  " + Prefix + "Ads ([IsType],[AdClassID], [AdName], [AdPic], [AdAddress], [AdText], [IsShow], [OrderID], [AddTime],[ServerName],[DownLoadLink],[DownLoadUrl],[OpenTime],[EditionInfor],[HomeLink],[HomeLinkUrl],[DisplayType],[UpdateTime],[AdminID],[AdvanceTime])");
                sql.Append("values(@IsType,@AdClassID, @AdName, @AdPic, @AdAddress, @AdText, @IsShow, @OrderID, @AddTime, @ServerName, @DownLoadLink, @DownLoadUrl, @OpenTime, @EditionInfor, @HomeLink, @HomeLinkUrl, @DisplayType, @UpdateTime, @AdminID, @AdvanceTime)");
            }
            else
            {
                sql.Append("insert into  " + Prefix + "Ads ([IsType],[AdClassID], [AdName],  [AdAddress], [AdText], [IsShow], [OrderID], [AddTime],[ServerName],[DownLoadLink],[DownLoadUrl],[OpenTime],[EditionInfor],[HomeLink],[HomeLinkUrl],[DisplayType],[UpdateTime],[AdminID],[AdvanceTime])");
                sql.Append("values(@IsType,@AdClassID, @AdName, @AdAddress, @AdText, @IsShow, @OrderID, @AddTime, @ServerName, @DownLoadLink, @DownLoadUrl, @OpenTime, @EditionInfor, @HomeLink, @HomeLinkUrl, @DisplayType, @UpdateTime, @AdminID ,@AdvanceTime)");
            }
            
            SqlParameter[] pra = {
                            new SqlParameter("@IsType",obj.IsType),
                            new SqlParameter("@AdClassID",obj.AdClassID),
							new SqlParameter("@AdName",obj.AdName),
							new SqlParameter("@AdPic",obj.AdPic),
							new SqlParameter("@AdAddress",obj.AdAddress),
							new SqlParameter("@AdText",obj.AdText),
							new SqlParameter("@IsShow",obj.IsShow),
							new SqlParameter("@OrderID",obj.OrderID),
							new SqlParameter("@AddTime",obj.AddTime),

                            new SqlParameter("@ServerName",obj.ServerName),
                            new SqlParameter("@DownLoadLink",obj.DownLoadLink),
                            new SqlParameter("@DownLoadUrl",obj.DownLoadUrl),
                            new SqlParameter("@OpenTime",obj.OpenTime),
                            new SqlParameter("@EditionInfor",obj.EditionInfor),
                            new SqlParameter("@HomeLink",obj.HomeLink),
                            new SqlParameter("@HomeLinkUrl",obj.HomeLinkUrl),
                            new SqlParameter("@DisplayType",obj.DisplayType),
                            new SqlParameter("@UpdateTime",obj.UpdateTime),
                            new SqlParameter("@AdminID",obj.AdminID),
                            new SqlParameter("@AdvanceTime",obj.AdvanceTime)
                                 };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }

        public int Update(Model.CNVP_Ads obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("update " + Prefix + "Ads set [IsType]=@IsType,[AdClassID] = @AdClassID,[AdName] = @AdName,[AdPic] = @AdPic,[AdAddress] = @AdAddress,[AdText] = @AdText,[IsShow] = @IsShow,[OrderID] = @OrderID,[ServerName]=@ServerName ,[DownLoadLink]=@DownLoadLink ,[DownLoadUrl]=@DownLoadUrl ,[OpenTime]=@OpenTime ,[EditionInfor]=@EditionInfor ,[HomeLink]=@HomeLink ,[HomeLinkUrl]=@HomeLinkUrl ,[DisplayType]=@DisplayType ,[UpdateTime]=@UpdateTime ,[AdminID]=@AdminID ,[AdvanceTime]=@AdvanceTime where [ID] = @ID");

            SqlParameter[] pra = { 
                            new SqlParameter("@ID",obj.ID),
                            new SqlParameter("@IsType",obj.IsType),
							new SqlParameter("@AdClassID",obj.AdClassID),
							new SqlParameter("@AdName",obj.AdName),
							new SqlParameter("@AdPic",obj.AdPic),
							new SqlParameter("@AdAddress",obj.AdAddress),
							new SqlParameter("@AdText",obj.AdText),
							new SqlParameter("@IsShow",obj.IsShow),
							new SqlParameter("@OrderID",obj.OrderID),

                            new SqlParameter("@ServerName",obj.ServerName),
                            new SqlParameter("@DownLoadLink",obj.DownLoadLink),
                            new SqlParameter("@DownLoadUrl",obj.DownLoadUrl),
                            new SqlParameter("@OpenTime",obj.OpenTime),
                            new SqlParameter("@EditionInfor",obj.EditionInfor),
                            new SqlParameter("@HomeLink",obj.HomeLink),
                            new SqlParameter("@HomeLinkUrl",obj.HomeLinkUrl),
                            new SqlParameter("@DisplayType",obj.DisplayType),
                            new SqlParameter("@UpdateTime",obj.UpdateTime),
                            new SqlParameter("@AdminID",obj.AdminID),
                            new SqlParameter("@AdvanceTime",obj.AdvanceTime)
                                };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }

        public int Delete(Model.CNVP_Ads obj)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("delete from " + Prefix + "Ads where [ID] = @ID");
            SqlParameter[] pra = {
                                new SqlParameter("@ID",obj.ID)
						
                                };
            return DbHelper.ExecuteNonQuery(sql.ToString(), pra);
        }

        public List<Model.CNVP_Ads> LoadAll(string sqlWhere)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select [ID],[IsType],[AdClassID], [AdName], [AdPic], [AdAddress], [AdText], [IsShow], [OrderID], [AddTime] ,[ServerName],[DownLoadLink],[DownLoadUrl],[OpenTime],[EditionInfor],[HomeLink],[HomeLinkUrl],[DisplayType],[UpdateTime],[AdminID],[AdvanceTime] from " + Prefix + "Ads");
            if (sqlWhere.Trim() != "")
            {
                sql.Append(" where " + sqlWhere);
            }
            DataTable DT = DbHelper.ExecuteTable(sql.ToString());
            List<Model.CNVP_Ads> list = new List<Model.CNVP_Ads>();
            if (DT != null && DT.Rows.Count > 0)
            {
                foreach (DataRow dr in DT.Rows)
                {
                    Model.CNVP_Ads cnvp_ads = new Model.CNVP_Ads();
                    cnvp_ads.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                    cnvp_ads.IsType = dr["IsType"] is DBNull ? (byte)0 : (byte)dr["IsType"];
                    cnvp_ads.AdClassID = dr["AdClassID"] is DBNull ? 0 : (int)dr["AdClassID"];
                    cnvp_ads.AdName = dr["AdName"] is DBNull ? String.Empty : (string)dr["AdName"];
                    cnvp_ads.AdPic = dr["AdPic"] is DBNull ? String.Empty : (string)dr["AdPic"];
                    cnvp_ads.AdAddress = dr["AdAddress"] is DBNull ? String.Empty : (string)dr["AdAddress"];
                    cnvp_ads.AdText = dr["AdText"] is DBNull ? String.Empty : (string)dr["AdText"];
                    cnvp_ads.IsShow = dr["IsShow"] is DBNull ? (byte)0 : (byte)dr["IsShow"];
                    cnvp_ads.OrderID = dr["OrderID"] is DBNull ? 0 : (int)dr["OrderID"];
                    cnvp_ads.AddTime = dr["AddTime"] is DBNull ? DateTime.Now : (DateTime)dr["AddTime"];

                    cnvp_ads.ServerName = dr["ServerName"] is DBNull ? String.Empty : (string)dr["ServerName"];
                    cnvp_ads.DownLoadLink = dr["DownLoadLink"] is DBNull ? String.Empty : (string)dr["DownLoadLink"];
                    cnvp_ads.DownLoadUrl = dr["DownLoadUrl"] is DBNull ? String.Empty : (string)dr["DownLoadUrl"];
                    cnvp_ads.OpenTime = dr["OpenTime"] is DBNull ? String.Empty : (string)dr["OpenTime"];
                    cnvp_ads.EditionInfor = dr["EditionInfor"] is DBNull ? String.Empty : (string)dr["EditionInfor"];
                    cnvp_ads.HomeLink = dr["HomeLink"] is DBNull ? String.Empty : (string)dr["HomeLink"];
                    cnvp_ads.HomeLinkUrl = dr["HomeLinkUrl"] is DBNull ? String.Empty : (string)dr["HomeLinkUrl"];
                    cnvp_ads.DisplayType = dr["DisplayType"] is DBNull ? 0 : (int)dr["DisplayType"];
                    cnvp_ads.UpdateTime = dr["UpdateTime"] is DBNull ? String.Empty : (string)dr["UpdateTime"];
                    cnvp_ads.AdminID = dr["AdminID"] is DBNull ? 0 : (int)dr["AdminID"];
                    cnvp_ads.AdvanceTime = dr["AdvanceTime"] is DBNull ? String.Empty : (string)dr["AdvanceTime"];
                    list.Add(cnvp_ads);
                }
            }
            return list;

        }

        public List<Model.CNVP_Ads> LoadAll(int topNum, string sqlWhere)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select top " + topNum + " [ID],[IsType],[AdClassID], [AdName], [AdPic], [AdAddress], [AdText], [IsShow], [OrderID], [AddTime] ,[ServerName],[DownLoadLink],[DownLoadUrl],[OpenTime],[EditionInfor],[HomeLink],[HomeLinkUrl],[DisplayType],[UpdateTime],[AdminID],[AdvanceTime] from " + Prefix + "Ads");
            if (sqlWhere.Trim() != "")
            {
                sql.Append(" where " + sqlWhere);
            }
            DataTable DT = DbHelper.ExecuteTable(sql.ToString());
            List<Model.CNVP_Ads> list = new List<Model.CNVP_Ads>();
            if (DT != null && DT.Rows.Count > 0)
            {
                foreach (DataRow dr in DT.Rows)
                {
                    Model.CNVP_Ads cnvp_ads = new Model.CNVP_Ads();
                    cnvp_ads.ID = dr["ID"] is DBNull ? 0 : (int)dr["ID"];
                    cnvp_ads.IsType = dr["IsType"] is DBNull ? (byte)0 : (byte)dr["IsType"];
                    cnvp_ads.AdClassID = dr["AdClassID"] is DBNull ? 0 : (int)dr["AdClassID"];
                    cnvp_ads.AdName = dr["AdName"] is DBNull ? String.Empty : (string)dr["AdName"];
                    cnvp_ads.AdPic = dr["AdPic"] is DBNull ? String.Empty : (string)dr["AdPic"];
                    cnvp_ads.AdAddress = dr["AdAddress"] is DBNull ? String.Empty : (string)dr["AdAddress"];
                    cnvp_ads.AdText = dr["AdText"] is DBNull ? String.Empty : (string)dr["AdText"];
                    cnvp_ads.IsShow = dr["IsShow"] is DBNull ? (byte)0 : (byte)dr["IsShow"];
                    cnvp_ads.OrderID = dr["OrderID"] is DBNull ? 0 : (int)dr["OrderID"];
                    cnvp_ads.AddTime = dr["AddTime"] is DBNull ? DateTime.Now : (DateTime)dr["AddTime"];

                    cnvp_ads.ServerName = dr["ServerName"] is DBNull ? String.Empty : (string)dr["ServerName"];
                    cnvp_ads.DownLoadLink = dr["DownLoadLink"] is DBNull ? String.Empty : (string)dr["DownLoadLink"];
                    cnvp_ads.DownLoadUrl = dr["DownLoadUrl"] is DBNull ? String.Empty : (string)dr["DownLoadUrl"];
                    cnvp_ads.OpenTime = dr["OpenTime"] is DBNull ? String.Empty : (string)dr["OpenTime"];
                    cnvp_ads.EditionInfor = dr["EditionInfor"] is DBNull ? String.Empty : (string)dr["EditionInfor"];
                    cnvp_ads.HomeLink = dr["HomeLink"] is DBNull ? String.Empty : (string)dr["HomeLink"];
                    cnvp_ads.HomeLinkUrl = dr["HomeLinkUrl"] is DBNull ? String.Empty : (string)dr["HomeLinkUrl"];
                    cnvp_ads.DisplayType = dr["DisplayType"] is DBNull ? 0 : (int)dr["DisplayType"];
                    cnvp_ads.UpdateTime = dr["UpdateTime"] is DBNull ? String.Empty : (string)dr["UpdateTime"];
                    cnvp_ads.AdminID = dr["AdminID"] is DBNull ? 0 : (int)dr["AdminID"];
                    cnvp_ads.AdvanceTime = dr["AdvanceTime"] is DBNull ? String.Empty : (string)dr["AdvanceTime"];
                    list.Add(cnvp_ads);
                }
            }
            return list;

        }

        public Model.CNVP_Ads LoadById(int ID)
        {

            StringBuilder sql = new StringBuilder();
            sql.Append("select [IsType],[AdClassID], [AdName], [AdPic], [AdAddress], [AdText], [IsShow], [OrderID], [AddTime] ,[ServerName],[DownLoadLink],[DownLoadUrl],[OpenTime],[EditionInfor],[HomeLink],[HomeLinkUrl],[DisplayType],[UpdateTime],[AdminID],[AdvanceTime] from " + Prefix + "Ads");
            sql.Append(" where [ID] = @ID");
            SqlParameter[] pra = {
                                 new SqlParameter("@ID",ID)
						
                                    };
            DataTable DT = DbHelper.ExecuteTable(sql.ToString(), pra);
            Model.CNVP_Ads cnvp_ads = new Model.CNVP_Ads();
            if (DT != null && DT.Rows.Count > 0)
            {
                DataRow dr = DT.Rows[0];
                cnvp_ads.IsType = dr["IsType"] is DBNull ? (byte)0 : (byte)dr["IsType"];
                cnvp_ads.AdClassID = dr["AdClassID"] is DBNull ? 0 : (int)dr["AdClassID"];
                cnvp_ads.AdName = dr["AdName"] is DBNull ? String.Empty : (string)dr["AdName"];
                cnvp_ads.AdPic = dr["AdPic"] is DBNull ? String.Empty : (string)dr["AdPic"];
                cnvp_ads.AdAddress = dr["AdAddress"] is DBNull ? String.Empty : (string)dr["AdAddress"];
                cnvp_ads.AdText = dr["AdText"] is DBNull ? String.Empty : (string)dr["AdText"];
                cnvp_ads.IsShow = dr["IsShow"] is DBNull ? (byte)0 : (byte)dr["IsShow"];
                cnvp_ads.OrderID = dr["OrderID"] is DBNull ? 0 : (int)dr["OrderID"];
                cnvp_ads.AddTime = dr["AddTime"] is DBNull ? DateTime.Now : (DateTime)dr["AddTime"];

                cnvp_ads.ServerName = dr["ServerName"] is DBNull ? String.Empty : (string)dr["ServerName"];
                cnvp_ads.DownLoadLink = dr["DownLoadLink"] is DBNull ? String.Empty : (string)dr["DownLoadLink"];
                cnvp_ads.DownLoadUrl = dr["DownLoadUrl"] is DBNull ? String.Empty : (string)dr["DownLoadUrl"];
                cnvp_ads.OpenTime = dr["OpenTime"] is DBNull ? String.Empty : (string)dr["OpenTime"];
                cnvp_ads.EditionInfor = dr["EditionInfor"] is DBNull ? String.Empty : (string)dr["EditionInfor"];
                cnvp_ads.HomeLink = dr["HomeLink"] is DBNull ? String.Empty : (string)dr["HomeLink"];
                cnvp_ads.HomeLinkUrl = dr["HomeLinkUrl"] is DBNull ? String.Empty : (string)dr["HomeLinkUrl"];
                cnvp_ads.DisplayType = dr["DisplayType"] is DBNull ? 0 : (int)dr["DisplayType"];
                cnvp_ads.UpdateTime = dr["UpdateTime"] is DBNull ? String.Empty : (string)dr["UpdateTime"];
                cnvp_ads.AdminID = dr["AdminID"] is DBNull ? 0 : (int)dr["AdminID"];
                cnvp_ads.AdvanceTime = dr["AdvanceTime"] is DBNull ? String.Empty : (string)dr["AdvanceTime"];
            }
            return cnvp_ads;
        }

        #endregion

        public DataTable GetPage(int PageIndex, int PageSize, out int RecordCount, out int PageCount, params SQLConditionInfo[] SqlCondition)
        {
            try
            {
                string AllFields = "a.[ID],a.[IsType], a.[AdName], a.[AdAddress],a.[IsShow], a.[OrderID],b.[ColumnName]";
                string Condition = "[" + Prefix + "Ads] as a left join [" + Prefix + "Column] as b on a.[AdClassID]=b.[ID]  where b.[ID]!=105";
                string IndexField = "a.[ID]";
                string OrderFields = "order by a.[OrderID], a.[ID] desc";
                return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }

        }

        public DataTable GetAdvanceAds(int PageIndex, int PageSize, out int RecordCount, out int PageCount, params SQLConditionInfo[] SqlCondition)
        {
            try
            {
                string AllFields = "[ID],[IsType],[AdClassID], [AdName], [AdPic], [AdAddress], [AdText], [IsShow], [OrderID], [AddTime] ,[ServerName],[DownLoadLink],[DownLoadUrl],[OpenTime],[EditionInfor],[HomeLink],[HomeLinkUrl],[DisplayType],[UpdateTime],[AdminID],[AdvanceTime]";
                string Condition = "[" + Prefix + "Ads]  where AdClassID=105 and IsShow=1";
                string IndexField = "[ID]";
                string OrderFields = "order by [AddTime] DESC";
                return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, null);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }
        }
        #region 时间戳获得信息
        //Paramter:dt1 时间戳 dat2 时间戳 wsql 其他where条件（也可加入ORDER ,GROUP BY等）
        //return : List<Model.CNVP_Ads> 数据集；
        public List<Model.CNVP_Ads> GetListForStamp(string dt1,string dt2,string wsql)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(" 1=1 ");
            long datea = long.Parse(dt1);
            long dateb = long.Parse(dt2);
            if (datea > dateb) {

                long datec = datea;
                datea = dateb;
                dateb = datec;
            }
            if (datea == dateb)
            {


                sql.Append(" AdvanceTime = '");
                sql.Append(datea);
                sql.Append("'");

            }
            else {

                sql.Append(datea);
                sql.Append("<");
                sql.Append("AdvanceTime");
                sql.Append("<");
                sql.Append(dateb);
            
            }
            if (wsql.Length > 1) {
                sql.Append(" and ");
                sql.Append(wsql);
            }
            return this.LoadAll(sql.ToString()); ;
        }

        public DataTable GetPageForStamp(int PageIndex, int PageSize, out int RecordCount, out int PageCount, params SQLConditionInfo[] SqlCondition)
        {

            try
            {
                string AllFields = "[ID],[IsType],[AdClassID], [AdName], [AdPic], [AdAddress], [AdText], [IsShow], [OrderID], [AddTime] ,[ServerName],[DownLoadLink],[DownLoadUrl],[OpenTime],[EditionInfor],[HomeLink],[HomeLinkUrl],[DisplayType],[UpdateTime],[AdminID],[AdvanceTime]";
                string Condition = "[" + Prefix + "Ads]  where ";
                StringBuilder sbsql = new StringBuilder();
                sbsql.Append("AdClassID=105 and IsShow=1");
                if (SqlCondition[0].value != null && SqlCondition[0].value.ToString() != ""&&SqlCondition[1].value != null && SqlCondition[1].value.ToString() != "")
                {
                    long datea = long.Parse(SqlCondition[0].value.ToString());
                    long dateb = long.Parse(SqlCondition[1].value.ToString());
                    if (datea > dateb)
                    {

                        long datec = datea;
                        datea = dateb;
                        dateb = datec;
                    }
                    if (datea == dateb)
                    {


                        sbsql.Append(" and AdvanceTime = '");
                        sbsql.Append(datea);
                        sbsql.Append("'");

                    }
                    else
                    {
                        sbsql.Append(" and ");
                        sbsql.Append(datea);
                        sbsql.Append("<");
                        sbsql.Append("AdvanceTime");
                        sbsql.Append(" and AdvanceTime");
                        sbsql.Append("<");
                        sbsql.Append(dateb);
                        sbsql.Append(" ");

                    }
                }
                
                if (SqlCondition[2].value != null && SqlCondition[2].value.ToString() != "")
                {
                    if (SqlCondition[2].value.ToString() == "0")
                    { } 
                    else 
                    { sbsql.Append(" and DisplayType = " + SqlCondition[2].value.ToString()); }
                }
                Condition += sbsql.ToString();
                string IndexField = "[ID]";
                string OrderFields = "order by [ID] ,[OrderID] desc";
                return DbHelper.ExecutePage(AllFields, Condition, IndexField, OrderFields, PageIndex, PageSize, out RecordCount, out PageCount, null);
            }
            catch (Exception e)
            {
                throw new Exception("用于分页的SQL语句无效:" + e.Message);
            }
        
        }
        #endregion
    }
}
